package com.demo.service;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.HashSet;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.Set;

import javax.sql.DataSource;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.datasource.DataSourceUtils;
import org.springframework.stereotype.Service;

@Service
public class CommonDataService {
	
	@Autowired
	private DataSource ds;
	
	public boolean createTable(String tableName, Set<String> columnNameSet){
		boolean result = false;
		Connection conn = null;
		Statement stmt = null;
		try {
			conn = DataSourceUtils.doGetConnection(ds);
			conn.setAutoCommit(false);
			stmt = conn.createStatement();
			String deleteTableSql = "DROP TABLE IF EXISTS `"+tableName+"`;";
			stmt.addBatch(deleteTableSql.toString());
			
			StringBuilder createTableSql = new StringBuilder(5000);
			createTableSql.append("CREATE TABLE `");
			createTableSql.append(tableName);
			createTableSql.append("` (");
			createTableSql.append("`id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键',");
			for (Iterator<String> iterator = columnNameSet.iterator(); iterator.hasNext();) {
				String columnName = (String) iterator.next();
				createTableSql.append("`");
				createTableSql.append(columnName);
				createTableSql.append("` varchar(200),");
			}
			createTableSql.append("PRIMARY KEY (`id`)");
			createTableSql.append(") ENGINE=InnoDB DEFAULT CHARSET=utf8;");
			stmt.addBatch(createTableSql.toString());
			
			stmt.executeBatch();
			conn.commit();
			result = true;
		} catch (Exception e) {
			if (conn !=null) {
				try {
					conn.rollback();
				} catch (SQLException e1) {
					e1.printStackTrace();
				}
			}
		}finally{
			if (stmt !=null) {
				try {
					stmt.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			if (conn !=null) {
				try {
					DataSourceUtils.doReleaseConnection(conn, ds);
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
		return result;
	}
	
	public int batchInsert(String tableName, List<Map<String, String>> columnValueList){
		Set<String> columnNameSet = new HashSet<String>();
		//todo 这里需要根据表名查询数据库，得到列的集合
		if (columnNameSet.contains("id") || columnNameSet.contains("ID")) {
			columnNameSet.remove("id");
		}
		if (columnNameSet.contains("ID")) {
			columnNameSet.remove("ID");
		}
		return batchInsert(tableName, columnNameSet, columnValueList);
	}
	
	public int batchInsert(String tableName, Set<String> columnNameSet, List<Map<String, String>> columnValueList){
		List<String> columnNameList  = new ArrayList<String>(columnNameSet);
		int result = 0;
		if (columnNameList.contains("id") || columnNameList.contains("ID")) {
			columnNameList.remove("id");
		}
		if (columnNameList.contains("ID")) {
			columnNameList.remove("ID");
		}
		Connection conn = null;
		Statement stmt = null;
		try {
			conn = DataSourceUtils.doGetConnection(ds);
			conn.setAutoCommit(false);
			stmt = conn.createStatement();
			StringBuilder sb = new StringBuilder(5000);
			sb.append("INSERT IGNORE INTO `");
			sb.append(tableName);
			sb.append("` (");
			for (int i=0;i<columnNameList.size();i++) {
				sb.append("`");
				sb.append(columnNameList.get(i));
				sb.append("`,");
			}
			sb.deleteCharAt(sb.length()-1);
			sb.append(")"); 
			sb.append(" VALUES "); 
			sb.append("("); 
			
			for (int i=0;i<columnValueList.size();i++) {
				Map<String, String> columnValueMap = columnValueList.get(i);
				StringBuilder sql = new StringBuilder(sb);
				for (int j = 0; j < columnNameList.size(); j++) {
					sql.append("'");
					String column = columnValueMap.get(columnNameList.get(j));
					sql.append(column==null?"":column);
					sql.append("',");
				}
				sql.deleteCharAt(sql.length()-1);
				sql.append(");"); 
				stmt.addBatch(sql.toString());
			}
			int[] resultArray = stmt.executeBatch();
			for (int i = 0; i < resultArray.length; i++) {
				result += resultArray[i];
			}
			conn.commit();
		} catch (Exception e) {
			if (conn !=null) {
				try {
					conn.rollback();
				} catch (SQLException e1) {
					e1.printStackTrace();
				}
			}
		}finally{
			if (stmt !=null) {
				try {
					stmt.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			if (conn !=null) {
				try {
					DataSourceUtils.doReleaseConnection(conn, ds);
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}			
		}
		return result;
	}

	public Map<String, List<String>> analyzeDiff(String tableName, List<String> sharedColumnNames) {
		Map<String, List<String>> diffMap = new HashMap<String, List<String>>();
		for (Iterator<String> iterator = sharedColumnNames.iterator(); iterator.hasNext();) {
			String columnName = iterator.next();
			if (columnName.equalsIgnoreCase("LAST_MODIFIED_TIME")) {
				continue;
			}
			List<String> ids = analyzeDiff(tableName, columnName);
			if (ids != null && ids.size()>0) {
				diffMap.put(columnName, ids);
			}
		}
		return diffMap;
	}

	private List<String> analyzeDiff(String tableName, String columnName) {
		List<String> ids = new ArrayList<String>();
		Connection conn = null;
		Statement stmt = null;
		ResultSet rs = null;
		try {
			conn = DataSourceUtils.doGetConnection(ds);
			stmt = conn.createStatement();
			String sql = "";
			if (columnName.equalsIgnoreCase("INVOICE_TIME")) {
				//对于表WP_STOCK_COST_MASTER的INVOICE_TIME字段特殊处理，屏蔽Timestamp与Date类型的差异
				sql = "select t2.pkid from (select t.pkid,COUNT(DISTINCT left(t."+columnName+",19)) as 'diffColumnName' from "+tableName+" t group by t.pkid having COUNT(1)>1 and COUNT(DISTINCT left(t.LAST_MODIFIED_TIME,19))=1 AND COUNT(DISTINCT t.dw_or_erp) = 2) t2 where t2.diffColumnName>1 ORDER BY t2.pkid";
			}else{
				sql = "select t2.pkid from (select t.pkid,COUNT(DISTINCT t."+columnName+") as 'diffColumnName' from "+tableName+" t group by t.pkid having COUNT(1)>1 and COUNT(DISTINCT left(t.LAST_MODIFIED_TIME,19))=1 AND COUNT(DISTINCT t.dw_or_erp) = 2) t2 where t2.diffColumnName>1 ORDER BY t2.pkid";
			}
			
			rs = stmt.executeQuery(sql);
			while (rs.next()) {
				ids.add(rs.getString(1));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			if (rs !=null) {
				try {
					rs.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			if (stmt !=null) {
				try {
					stmt.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			if (conn !=null) {
				try {
					DataSourceUtils.doReleaseConnection(conn, ds);
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}			
		}
		return ids;
	}
	
	public List<String> getDiffIds(String tableName) {
		List<String> diffIds = new ArrayList<String>();
		Connection conn = null;
		Statement stmt = null;
		ResultSet rs = null;
		try {
			conn = DataSourceUtils.doGetConnection(ds);
			stmt = conn.createStatement();
			String sql = "SELECT t.pkid FROM "+tableName+" t GROUP BY t.pkid HAVING COUNT(1) > 1 AND COUNT(DISTINCT t.dw_or_erp) = 2 AND COUNT( DISTINCT left(t.LAST_MODIFIED_TIME,19) ) = 1 ORDER BY t.pkid";
			rs = stmt.executeQuery(sql);
			while (rs.next()) {
				diffIds.add(rs.getString(1));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			if (rs != null) {
				try {
					rs.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			if (stmt != null) {
				try {
					stmt.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			if (conn !=null) {
				try {
					DataSourceUtils.doReleaseConnection(conn, ds);
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}			
		}
		return diffIds;
	}
	
}
